SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

--================================================
--  Create Inline Table-valued Function template
--================================================



CREATE FUNCTION [dbo].[tvfNAVDatesByFund]
    (
      @refDate DATE ,
      @DateOffset INT ,
      @BothDirections BIT ,
      @ReturnOriginalDate BIT
    )
RETURNS @tbl TABLE ( NAVDate DATE )
AS 
    BEGIN



--DECLARE @RefDate DATE = '8/19/2013'
--DECLARE @DateOffset AS INT = -3
--DECLARE @BothDirections AS bit = 1
        DECLARE @StartRow AS INT 
        DECLARE @EndRow AS INT 
        DECLARE @OrigRow INT = ( SELECT tb2.rown
                                 FROM   ( SELECT    ROW_NUMBER() OVER ( ORDER BY tmpV.NAVDate ) AS rowN ,
                                                    tmpV.NAVDate ,
                                                    tmpV.FundId
                                          FROM      dbo.view_NAVDates tmpV
                                          WHERE     tmpv.FundId = 'FSIC'
                                        ) AS tb2
                                 WHERE  tb2.NAVDate = @refDate
                               ) 

	
        SELECT  @DateOffset = ( SELECT  CASE WHEN @bothDirections = 1
                                             THEN ABS(@DateOffset)
                                             ELSE @DateOffset
                                        END AS d
                              )
        SELECT  @OrigRow = ( SELECT CASE WHEN @BothDirections = 1
                                         THEN @OrigRow - @DateOffset
                                         ELSE @OrigRow
                                    END AS ow
                           ) 
	

        SELECT  @StartRow = ( SELECT    CASE WHEN ( @OrigRow + @DateOffset ) > @OrigRow
                                             THEN @OrigRow
                                             ELSE ( @OrigRow + @dateOffseT )
                                        END AS st
                            )
		
		
        SELECT  @EndRow = ( SELECT  CASE WHEN ( @OrigRow + @DateOffset ) > @OrigRow
                                         THEN ( @OrigRow + @DateOffset )
                                         ELSE ( @OrigRow )
                                    END AS st
                          )
        SELECT  @EndRow = ( SELECT  CASE WHEN @BothDirections = 1
                                         THEN @EndRow + @DateOffset
                                         ELSE @EndRow
                                    END AS c
                          )

        INSERT  INTO @tbl
                ( NAVDate 
                )
                SELECT  tmp.NAVDate
                FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY tmpV.NAVDate ) AS rowN ,
                                    tmpV.NAVDate ,
                                    tmpV.FundId
                          FROM      dbo.view_NAVDates tmpV
                          WHERE     tmpv.FundId = 'FSIC'
                        ) AS tmp
                WHERE   tmp.rowN >= @StartRow
                        AND tmp.rowN <= @EndRow
                        AND tmp.NAVDate <> ( CASE WHEN @ReturnOriginalDate = 0
                                                       AND @DateOffset <> 0
                                                  THEN @refDate
                                                  ELSE '1/1/1900'
                                             END )

        RETURN
    END   

	




GO
